/**   
* @Title: MetadataUpdateService.java 
* @Package com.medsoft.logpool.core 
* @Description: TODO(用一句话描述该文件做什么) 
* @author zjhua@hundsun.com   
* @date 2016年7月12日 上午9:15:32 
* @version V1.0   
*/ 
package com.medsoft.perfstat.service;

import java.util.ArrayList;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import com.cyl.kernel.util.Base64Util;
import com.cyl.kernel.util.DateUtil;
import com.cyl.kernel.util.SSHHelper;
import com.cyl.kernel.util.UnitConvertUtil;
import com.medsoft.perfstat.dao.PerfStatSrcDAO;
import com.medsoft.perfstat.pojo.Apps;
import com.medsoft.perfstat.pojo.ISGlobalStatus;
import com.medsoft.perfstat.pojo.ISGlobalVariable;
import com.medsoft.perfstat.pojo.ISIndexStatistics;
import com.medsoft.perfstat.pojo.ISTableStatistics;
import com.medsoft.perfstat.pojo.InnodbBufferPoolStats;
import com.medsoft.perfstat.pojo.PSEventsStatementsSummaryByDigest;
import com.medsoft.perfstat.pojo.PSEventsWaitsSummaryGlobalByEventName;
import com.medsoft.perfstat.pojo.PSTableIOWaitsSummaryByIndexUsage;
import com.medsoft.perfstat.pojo.PSTableIOWaitsSummaryByTable;
import com.medsoft.perfstat.pojo.PSTableLockWaitsSummaryByTable;
import com.medsoft.perfstat.pojo.SlaveStatus;
import com.medsoft.perfstat.pojo.SlowQueryLogDigest;

/**
 * @author zjhua
 *
 */
@Service
public class PerfStatSrcService {
	static final Logger logger = LoggerFactory.getLogger(PerfStatSrcService.class);
	
	@Autowired
	private PerfStatSrcDAO perfStatSrcDAO;
	//@Transactional
	public List<ISGlobalStatus> queryISGlobalStatus(Apps app) {
		if(app.getVer().indexOf("5.6") >= 0) {
			return perfStatSrcDAO.queryISGlobalStatus();
		} else {
			return perfStatSrcDAO.queryISGlobalStatus57();
		}
		
	};
	//@Transactional
	public List<ISGlobalVariable> queryISGlobalVariables(Apps app) {
		if(app.getVer().indexOf("5.6") >= 0) {
			return perfStatSrcDAO.queryISGlobalVariables();
		} else {
			return perfStatSrcDAO.queryISGlobalVariables57();
		}
	};
	//@Transactional
	public List<PSTableIOWaitsSummaryByTable> queryPSTableIOWaitsSummaryByTable() {
		return perfStatSrcDAO.queryPSTableIOWaitsSummaryByTable();
	};
	//@Transactional
	public List<PSTableLockWaitsSummaryByTable> queryPSTableLockWaitsSummaryByTable() {
		return perfStatSrcDAO.queryPSTableLockWaitsSummaryByTable();
	};
	//@Transactional
	public List<PSTableIOWaitsSummaryByIndexUsage> queryPSTableIOWaitsSummaryByIndexUsage() {
		return perfStatSrcDAO.queryPSTableIOWaitsSummaryByIndexUsage();
	};
	//@Transactional
	public List<PSEventsStatementsSummaryByDigest> queryPSEventsStatementsSummaryByDigest() {
		return perfStatSrcDAO.queryPSEventsStatementsSummaryByDigest();
	};
	//@Transactional
	public List<ISIndexStatistics> queryISIndexStatistics() {
		return perfStatSrcDAO.queryISIndexStatistics();
	};
	//@Transactional
	public List<PSEventsWaitsSummaryGlobalByEventName> queryPSEventsWaitsSummaryGlobalByEventName() {
		return perfStatSrcDAO.queryPSEventsWaitsSummaryGlobalByEventName();
	};
	//@Transactional
	public List<ISTableStatistics> queryISTableStatistics() {
		return perfStatSrcDAO.queryISTableStatistics();
	}
	public List<SlaveStatus> querySlaveStatus() {
		return perfStatSrcDAO.querySlaveStatus();
	}
	public List<InnodbBufferPoolStats> queryInnodbBufferPoolStats() {
		return perfStatSrcDAO.queryInnodbBufferPoolStats();
	}
	public String[] getMySQLOSInfo(Apps app) {
		String cmd = "ps axo pid,ppid,comm,lstart,rss,bsdtime | grep `lsof -i:" + app.getPort() + " | grep LISTEN | awk '{print $2}'` | egrep -v 'grep' | awk '{print $9 \" \" $10 \" \" $8\"_\"$5\"_\"$6\"_\"$7}'";
		String result = SSHHelper.exec(app.getHostname(), app.getSshUsername(), Base64Util.getFromBase64(app.getSshPassword()), app.getSshPort(), cmd);
		return result.split("\\s+");
	}
	public long getOSUptime(Apps app) {
		String cmd = "cat /proc/uptime | awk '{print $1}'";
		long result = Math.round(Double.valueOf(SSHHelper.exec(app.getHostname(), app.getSshUsername(), Base64Util.getFromBase64(app.getSshPassword()), app.getSshPort(), cmd).trim()));
		return result;
	}
	public String[] getOSCPUTime(Apps app) {
		String cmd = "cat /proc/stat | grep 'cpu ' | awk '{print $2 \" \" $3 \" \" $4 \" \" $5 \" \" $6}'";
		String result = SSHHelper.exec(app.getHostname(), app.getSshUsername(), Base64Util.getFromBase64(app.getSshPassword()), app.getSshPort(), cmd).trim();
		return result.split("\\s+");
	}
	public int getOSCPUCount(Apps app) {
		String cmd = "cat /proc/cpuinfo | grep processor | wc -l";
		int result = Integer.valueOf(SSHHelper.exec(app.getHostname(), app.getSshUsername(), Base64Util.getFromBase64(app.getSshPassword()), app.getSshPort(), cmd).trim());
		return result;
	}
	public String[] getOSMemInfo(Apps app) {
		//centos 7中/proc/meminfo输出多了一个MemAvailable选项,会导致Swap错位而不正确
		String cmd = "cat /proc/meminfo | egrep 'Mem|Swap' | egrep -v 'Cached|Available' | awk '{printf($2) \" \"}' | awk '{print $1 \" \" $2 \" \" $3 \" \" $4}'";
		String result = SSHHelper.exec(app.getHostname(), app.getSshUsername(), Base64Util.getFromBase64(app.getSshPassword()), app.getSshPort(), cmd).trim();
		return result.split("\\s+");
	}
	
	public String renameSlowQueryLog(Apps app,String oldFileName) {
		String newFileName = oldFileName + "." + DateUtil.getDateYYYYMMDDHH24MI();
		String cmd = "mv " + oldFileName + " " + newFileName;
		String result = SSHHelper.exec(app.getHostname(), app.getSshUsername(), Base64Util.getFromBase64(app.getSshPassword()), app.getSshPort(), cmd).trim();
		return newFileName;
	}
	
	public String parseSlowQueryLog(Apps app,String fileName) {
		String cmd = "pt-query-digest " + fileName + " > " + fileName + ".analyzed";
		String result = SSHHelper.exec(app.getHostname(), app.getSshUsername(), Base64Util.getFromBase64(app.getSshPassword()), app.getSshPort(), cmd).trim();
		return fileName + ".analyzed";
	}
	
	public List<SlowQueryLogDigest> getSlowQueryLogSummary(Apps app,String fileName) {
		List<SlowQueryLogDigest> sqls = new ArrayList<SlowQueryLogDigest>();
		String cmd = "cat " + fileName + " | grep -A22 Profile | egrep \"[1-9] 0x\" | egrep -i \"insert | delete | update | select | replace | commit\" | sed -n '1,20p' | awk '{ printf(\"%s\",$3 \"~~\" $4 \"~~\" $6 \"~~\"); for(i=1; i<=8; i++){ $i=\"\" }; print $0 }'";
		String result = SSHHelper.exec(app.getHostname(), app.getSshUsername(), Base64Util.getFromBase64(app.getSshPassword()), app.getSshPort(), cmd).trim();
		if (result.length() > 0) {
			String[] sqlRecords = result.split("\n");
			for(int i=0;i<sqlRecords.length;i++) {
				System.out.println("sql record:[" + sqlRecords[i] + "]");
				String[] slowQueryLogDigest = sqlRecords[i].split("~~");
				SlowQueryLogDigest sql = new SlowQueryLogDigest();
				sql.setQueryId(slowQueryLogDigest[0]);
				sql.setTotalResponseTime(Double.parseDouble(slowQueryLogDigest[1]));
				sql.setCalls(Long.parseLong(slowQueryLogDigest[2]));
				sql.setAbbrSql(slowQueryLogDigest[3].trim());
				String firstWord = slowQueryLogDigest[3].trim().split("\\s+")[0];
				String subcmd = "cat " + fileName + " | egrep -A100 \"Query.*" + slowQueryLogDigest[0] + "\" | grep -iv \"show\" | sed -n \"1,35p\" | egrep -i \"^# Rows|Stored rout|" + firstWord + "\" | sed -n \"1,5p\"";
				try {
					String subResult = SSHHelper.exec(app.getHostname(), app.getSshUsername(), Base64Util.getFromBase64(app.getSshPassword()), app.getSshPort(), subcmd).trim();
					String subResults[] = subResult.split("\n");
					sql.setTotalRowsEffected(UnitConvertUtil.convert2Long(subResults[0].split("\\s+")[4]) + UnitConvertUtil.convert2Long(subResults[2].split("\\s+")[4]));
					sql.setTotalRowsExamined(UnitConvertUtil.convert2Long(subResults[1].split("\\s+")[4]));
					if(subResults.length == 4) {
						sql.setFullSql(subResults[3]);
					} else {
						sql.setFullSql("[" + subResults[3].substring(15) + "] " + subResults[4]);
					}
				} catch (Exception e) {
					logger.error("获取slow query明细出现异常：",e);
					sql.setTotalRowsEffected(0);
					sql.setTotalRowsExamined(0);
					sql.setFullSql("");
				}
				sqls.add(sql);
			}
		}
		return sqls;
	}
	
	public static void main1(String[] args) {
		PerfStatSrcService service = new PerfStatSrcService();
		Apps app = new Apps();
		app.setHostname("192.168.3.131");
		app.setSshPort(22);
		app.setSshUsername("root");
		app.setSshPassword("james@123");
		System.out.println("os cpu count: " + service.getOSCPUCount(app));
		System.out.println("os cpu time: " + StringUtils.arrayToCommaDelimitedString(service.getOSCPUTime(app)));
		System.out.println("os mem info: " + StringUtils.arrayToCommaDelimitedString(service.getOSMemInfo(app)));
		System.out.println("os uptime: " + service.getOSUptime(app));
		System.out.println("mysql cpu info: " + StringUtils.arrayToCommaDelimitedString(service.getMySQLOSInfo(app)));
	}
	
	public String disableSlowlogAndGetName(Apps app) {
		perfStatSrcDAO.disableSlowLog();
		if(app.getVer().equals("5.6")) {
			return perfStatSrcDAO.getSlowFileName();
		} else {
			return perfStatSrcDAO.getSlowFileName57();
		}
	}
	public void enableSlowLog() {
		perfStatSrcDAO.enableSlowLog();
	}
	
	public static void main(String[] args) {
		String ms = "# Rows examine  21 293.93k     174  37.25k  18.37k  28.66k   8.06k  18.47k";
		System.out.println(StringUtils.arrayToDelimitedString(ms.split("\\s+",-1),","));
		
		for(int i=0;i<"\n".split("\n").length;i++) {
			System.out.println("...");
		}
	}
	public void removeOldFile(Apps app, String rotateFileName, String analyzedFileName) {
		String cmd = "rm -rf " + rotateFileName;
		String result = SSHHelper.exec(app.getHostname(), app.getSshUsername(), Base64Util.getFromBase64(app.getSshPassword()), app.getSshPort(), cmd).trim();
	}
}
